package com.gugee.tiktok.web.repository;

import com.gugee.tiktok.web.dto.AwemeHashtagResponse;
import com.gugee.tiktok.web.model.mysql.AwemeHashtag;
import org.springframework.data.jdbc.repository.query.Query;
import org.springframework.data.repository.CrudRepository;

import java.util.List;

public interface AwemeHashtagRepository extends CrudRepository<AwemeHashtag,Long> {

    @Query("select hashtag_name,count(*) mention from aweme_hashtag where uid = :uid and create_time > :startTime group by hid order by mention desc")
    List<AwemeHashtagResponse> getAllByUidAndAwemeCreateTimeLessThan(long uid, long startTime);

    @Query("select " +
            "h.hashtag_name,count(*) mention,avg(a.o_play_count) avg_view," +
            "sum(a.o_digg_count+o_share_count+o_comment_count)/sum(a.o_play_count)*100 engagement_rate," +
            "(avg( a.o_play_count ) - b.o_avg_views)/ b.o_avg_views*100 performance  " +
            "from aweme_hashtag h " +
            "left join aweme a on h.aweme_id = o_aweme_id " +
            "left join blogger_main b ON a.o_uid = b.o_uid " +
            "where uid = :uid and h.is_commerce = 1 and " +
            "h.create_time > :startTime " +
            "group by h.hid order by mention desc " +
            "limit 0,:size")
    List<AwemeHashtagResponse> getTopByUidAndCreateTimeLessThanOrderByAvgView(long uid, long startTime, int size);
}
